/**
 * A JDBC based hash table test.  
 * 
 * Can write and read snObjects from a MySQL server.  Next step is to phase into
 * the code base.  Hazzah.
 * 
 */
package sxe.core.hash;

import snbenchlib.XML;
import snobject.MimeException;
import snobject.snObject;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.ArrayList;
import step.parse.SyntaxError;

   public class SQLClientHelper
   {
	   public static String NAME_COL = "DATA_NAME";
	   public static String TYPE_COL = "DATA_TYPE";
	   public static String VALUE_COL = "DATA_VALUE";
	   
	   private Connection conn = null;
	   private String userName = "snUser";
       private String password = "snPasswd1";
       private String url = "jdbc:mysql://resourcemonitor.bu.edu/snstore";
       
       private String tableName = null;
       
       public SQLClientHelper() throws SQLException{
    	   if(!LoadDriver())
    		   throw new SQLException("Failed to load the driver");
       }
       
	   public static boolean IsConfigured() {
	        try {
	            Class.forName("com.mysql.jdbc.Driver").newInstance();
	            return true;
	        } catch (Exception ex) {
	            // handle the error
	        	ex.printStackTrace();
	        	return false;
	        }
		}

       
       public class pair{
    	   String key;
    	   snObject val;
    	   public pair(String l, snObject r){
    		   key = l;
    		   val = r;
    	   }
    	   public String getKey(){
    		   return key;
    	   }
    	   public snObject getValue(){
    		   return val;
    	   }
    	   
       }
       
       public void Cleanup(){
    	   if (conn != null)
           {
               try
               {
                   conn.close ();
                   System.out.println ("Database connection terminated");
               }
               catch (Exception e) { /* ignore close errors */ }
           }
       }
	   public void Connect()throws SQLException{
		   conn = DriverManager.getConnection (url, userName, password);
           System.out.println ("Database connection established");
	   }
	   
	   public void SetAutoCommit(boolean val) throws SQLException{
		   conn.setAutoCommit(false);
	   }
	   public void Commit() throws SQLException{
		   conn.commit();
	   }
	   
	   public ArrayList<String> GetTableList() throws SQLException{
		   ArrayList<String> al = new ArrayList<String>();
		      
		   Statement s = conn.createStatement ();
		   s.executeQuery ("SHOW TABLES");
		   ResultSet rs = s.getResultSet ();
		   int count = 0;
		   while (rs.next ())
		   {
		       String nameVal = rs.getString (1);
		       ++count;
		       al.add(nameVal);
		   }
		   rs.close ();
		   s.close ();
		   System.out.println (count + " rows were retrieved");
		   
		   return al;
	   }
	   
	   public ArrayList<pair> GetValueList() throws SQLException{
		   ArrayList<pair> al = new ArrayList<pair>();
		      
		   Statement s = conn.createStatement ();
		   s.executeQuery ("SELECT " + NAME_COL + "," + VALUE_COL + " FROM " + tableName);
		   ResultSet rs = s.getResultSet ();
		   int count = 0;
		   while (rs.next ())
		   {
		       String nameVal = rs.getString (1);
		       byte[] imgBytes = rs.getBytes(2);
		       snObject snNew = null;
		       // use the data in some way here
		       try{
		    	   snNew =  snObject.ParseSnObject(imgBytes);
		       }
		       catch(MimeException ex){
		    	   ex.printStackTrace();
		       }
		       catch(SyntaxError ex){
		    	   ex.printStackTrace();
		       }
		       ++count;
		       al.add(new pair(nameVal,snNew));
		   }
		   rs.close ();
		   s.close ();
		   System.out.println (count + " rows were retrieved");
		   
		   return al;
	   }

	   public ArrayList<String> GetKeyList() throws SQLException{
		   ArrayList<String> al = new ArrayList<String>();
		      
		   Statement s = conn.createStatement ();
		   s.executeQuery ("SELECT " + NAME_COL + " FROM " + tableName);
		   ResultSet rs = s.getResultSet ();
		   int count = 0;
		   while (rs.next ())
		   {
		       String key = rs.getString (1);
		       ++count;
		       al.add(key);
		   }
		   rs.close ();
		   s.close ();
		   System.out.println (count + " rows were retrieved");
		   
		   return al;
	   }

	   
	   public void SetTable(String TableName){
		   tableName = TableName;
	   }
	   
	   public void CreateTable(String TableName)throws SQLException{
    	   tableName = TableName;

		   System.out.println ("Creating a table");
           Statement stmt = conn.createStatement();
                          
           stmt.executeUpdate("CREATE TABLE IF NOT EXISTS " + tableName +
        		    " (" + NAME_COL + " VARCHAR(32), " + TYPE_COL + " VARCHAR(32), " + VALUE_COL + " LONGBLOB)");
           System.out.println ("Created a table");
	   }

	   public void RemoveTable(String TableName)throws SQLException{
    	   tableName = TableName;

		   System.out.println ("Deleting a table");
           Statement stmt = conn.createStatement();
                          
           stmt.executeUpdate("DROP TABLE IF EXISTS " + tableName );
           System.out.println ("Deleted a table");
	   }

	   
	   public  void Put(snObject snObj, String name) throws SQLException{
		   if(tableName==null)
			   throw new SQLException("No table selected/active for this instance");
		   
		   System.out.println ("Inserting data into the table");
           
           //stmt.executeUpdate("INSERT INTO " + tablename + " VALUES ('KEY', )");
           PreparedStatement ps = conn.prepareStatement("INSERT INTO " + tableName + " VALUES (?, ?, ?)");
           ps.setString(1, name);
           ps.setString(2, snObj.getBinaryMimeType());
           ps.setString(3, new String(snObj.getXml()) );
            
           ps.executeUpdate();
           ps.close();
           System.out.println ("Inserted data into the table");		   
	   }
	   
	   /**
	    * Returns an snObject stored in the hash with name specified
	    * 
	    * Throws an exception if object can't be found, can't be parsed, etc.
	    * 
	    * @param name
	    * @return
	    * @throws SQLException
	    */
	   public  snObject Get(String name) throws SQLException {
		   if(tableName==null)
			   throw new SQLException("No table selected/active for this instance");

		   System.out.println ("Retrieving data named [" + name + "] from the table");
		   
		   PreparedStatement ps = conn.prepareStatement("SELECT " + VALUE_COL + " FROM " + tableName + " WHERE " + NAME_COL + " = ?");
		   ps.setString(1, name);
		   ResultSet rs = ps.executeQuery();
		   while (rs.next()) {
		       byte[] imgBytes = rs.getBytes(1);
		       System.out.println ("Got data [" + name + "] from the table");
		       // use the data in some way here
		       try{
		    	   snObject snNew = snObject.ParseSnObject(imgBytes);
				   rs.close();
				   ps.close();
			       return snNew;
		       }
		       catch(MimeException ex){
		    	   ex.printStackTrace();
		       }
		       catch(SyntaxError ex){
		    	   ex.printStackTrace();
		       }
		   }		   
		   rs.close();
		   ps.close();

		   return null;
	   }

	   public  snObject Remove(String name) throws SQLException {
		   if(tableName==null)
			   throw new SQLException("No table selected/active for this instance");

		   System.out.println ("Retrieving/Removing data named [" + name + "] from the table");
		   
		   PreparedStatement ps = conn.prepareStatement("SELECT " + VALUE_COL + " FROM " + tableName + " WHERE " + NAME_COL + " = ?");
		   ps.setString(1, name);
		   ResultSet rs = ps.executeQuery();
		   while (rs.next()) {
		       byte[] imgBytes = rs.getBytes(1);
		       System.out.println ("Got data [" + name + "] from the table");
		       // use the data in some way here
		       try{
		    	   snObject snNew = snObject.ParseSnObject(imgBytes);
				   rs.close();
				   ps.close();
			       return snNew;
		       }
		       catch(MimeException ex){
		    	   ex.printStackTrace();
		       }
		       catch(SyntaxError ex){
		    	   ex.printStackTrace();
		       }
		   }		   
		   rs.close();
		   ps.close();

		   return null;
	   }

	   
	   private boolean LoadDriver() {
	        try {
	            Class.forName("com.mysql.jdbc.Driver").newInstance();
	            return true;
	        } catch (Exception ex) {
	            // handle the error
	        	ex.printStackTrace();
	        	return false;
	        }
		}
	   
       public static void main (String[] args)
       {
    	   SQLClientHelper Tro = null;
           try
           {
        	   Tro = new SQLClientHelper();

        	   Tro.Connect();
        	   
        	   //Tro.CreateTable("SNBENCH");
               Tro.RemoveTable("SNBENCH");
               /*
               snBoolean snO = new snBoolean(true);
               
               Tro.Put(snO, "thisObject");
               snObject snNew = Tro.Get("thisObject");
			   if(snNew!=null)
				   System.out.println("Retrieved:\n" + new String(snNew.getXml()));
			   else{
				   System.out.println("Failed to parse object");
			   }
			   */               
           }
           catch (SQLException ex) {
               // handle any errors
               System.out.println("SQLException: " + ex.getMessage());
               System.out.println("SQLState: " + ex.getSQLState());
               System.out.println("VendorError: " + ex.getErrorCode());
           }
           finally
           {
               if(Tro!=null)
            	   Tro.Cleanup();
           }
       }

	public String getXML() {
		/**
		 * @return String of XML data describing this storage
		 * 
		 * Returns list of names -- NOT values.  Getting values 
		 * is way too time consuming.
		 */
        String b = "<storageData " +
                        "xmlns=\"" + XML.XMLNS + "\">\n";
        try{
	        if(conn!=null) {
	            for(String s : GetKeyList()) {
	                    b+="<KeyValPair " +
	                          "key=\"" + s + "\" " +
	                          "href=\"" + s + "/\" " +
	                          "xmlns=\"" + XML.XMLNS + "\">\n";
	                        b+="</KeyValPair>";
	                }
	        }
        }
        catch(SQLException sqe){
        	b+="<error/>";
        }
        b += "</storageData>\n";
        return b;

	}
   }